﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using GtsServer.Common;
using System.Collections.Generic;
using GtsServer.Model;

namespace GtsServer.DAL
{
    /// <summary>
    /// 数据访问类:Task
    /// </summary>
    public partial class TaskDAL
    {
        public TaskDAL()
        { }
        #region  Method

        /// <summary>
        /// 得到最大ID
        /// </summary>
        public int GetMaxId()
        {
            return DbHelperSQL.GetMaxID("ID", "Task");
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int ID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from Task");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
};
            parameters[0].Value = ID;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        public DataSet GetMatchList(string userAccount, int taskType)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@taskType",taskType)
            };
            DataSet ds = DbHelperSQL.RunProcedure("GetMatchList", parms, "GetMatchList");
            return ds;
        }

        public DataSet EditMatchScoreAndGold(string userAccount, int taskID, int taskScore, int gold, int isGet, int gameCount)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@taksID",taskID),
                new SqlParameter("@taskScore",taskScore),
                new SqlParameter("@MatchGold",gold),
                new SqlParameter("@isGet",isGet),
                new SqlParameter("@gameCount",gameCount)
            };
            DataSet ds = DbHelperSQL.RunProcedure("EditMatchTask", parms, "EditMatchTask");
            return ds;
        }

        public DataSet GetMatchRank(string userAccount, int taskID)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@userAccount",userAccount),
                new SqlParameter("@taskID",taskID)
            };
            DataSet ds = DbHelperSQL.RunProcedure("GetMatchCharts", parms, "GetMatchCharts");
            return ds;
        }


        public DataSet AddArrayTask(int taskID, int taskNum)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@taskID",taskID),
                new SqlParameter("@taskNum",taskNum)
            };
            DataSet ds = DbHelperSQL.RunProcedure("AddArraayTask", parms, "AddArraayTask");
            return ds;
        }


        public DataSet GetListByState()
        {
            string sql = "select * from Task where state<>" + Convert.ToInt32(EnumTaskState.已结束) + ";";
            sql += "select * from [User] where IsRobot = 1 and RobotIndex>0";
            return DbHelperSQL.Query(sql);
        }


        public DataSet GetListByTaskID(int taskID)
        {
            string sql = "select tu.UserID userID from TaskUser tu left join Task t on tu.TaskID =t.ID ";
            sql += " left join [User] u on u.ID =tu.UserID where tu.TaskID = " + taskID + " and u.IsRobot=1 and u.RobotIndex >0;";
            sql += "select  * from dbo.TaskRobotSetting where taskID =" + taskID;
            return DbHelperSQL.Query(sql);
        }

        public bool Update(string ids)
        {
            IDataParameter[] parms = new IDataParameter[]{ 
                new SqlParameter("@taskIDs",ids)
            };
            try
            {
                DbHelperSQL.RunProcedure("EndMatchHandPrize", parms, "EndMatchHandPrize");
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return false;
            }

        }

        public bool Update(string ids, int state)
        {
            string sql = "update task set State=" + state + " where id in(" + ids + ")";
            try
            {
                DbHelperSQL.ExecuteSql(sql);
                return true;
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog(ex);
                return false;
            }
        }

        public DataSet GetRobotSetting(string ids)
        {
            string sql = "select  * from dbo.TaskRobotSetting where taskID in (" + ids + ")";
            return DbHelperSQL.Query(sql);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(GtsServer.Model.Task model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into Task(");
            strSql.Append("Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage)");
            strSql.Append(" values (");
            strSql.Append("@Name,@TaskTypeID,@CrateTime,@StartTime,@EndTime,@IsRemind,@State,@IsAllVisual,@EmptyUnit,@IsReachCondition,@TaskContent,@TaskRemark,@ImageType,@InitGold,@SortNum,@IsUseStage)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@TaskTypeID", SqlDbType.Int,4),
					new SqlParameter("@CrateTime", SqlDbType.DateTime),
					new SqlParameter("@StartTime", SqlDbType.DateTime),
					new SqlParameter("@EndTime", SqlDbType.DateTime),
					new SqlParameter("@IsRemind", SqlDbType.Bit,1),
					new SqlParameter("@State", SqlDbType.Int,4),
					new SqlParameter("@IsAllVisual", SqlDbType.Bit,1),
					new SqlParameter("@EmptyUnit", SqlDbType.Int,4),
					new SqlParameter("@IsReachCondition", SqlDbType.Bit,1),
					new SqlParameter("@TaskContent", SqlDbType.VarChar,100),
					new SqlParameter("@TaskRemark", SqlDbType.VarChar,5000),
					new SqlParameter("@ImageType", SqlDbType.VarChar,50),
					new SqlParameter("@InitGold", SqlDbType.Int),
					new SqlParameter("@SortNum", SqlDbType.Int),
					new SqlParameter("@IsUseStage", SqlDbType.Bit,1)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.TaskTypeID;
            parameters[2].Value = model.CrateTime;
            parameters[3].Value = model.StartTime;
            parameters[4].Value = model.EndTime;
            parameters[5].Value = model.IsRemind;
            parameters[6].Value = model.State;
            parameters[7].Value = model.IsAllVisual;
            parameters[8].Value = model.EmptyUnit;
            parameters[9].Value = model.IsReachCondition;
            parameters[10].Value = model.TaskContent;
            parameters[11].Value = model.TaskRemark;
            parameters[12].Value = model.ImageType;
            parameters[13].Value = model.InitGold;
            parameters[14].Value = model.SortNum;
            parameters[15].Value = model.IsUseStage;

            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(GtsServer.Model.Task model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update Task set ");
            strSql.Append("Name=@Name,");
            strSql.Append("StartTime=@StartTime,");
            strSql.Append("EndTime=@EndTime,");
            strSql.Append("IsRemind=@IsRemind,IsAllVisual=@IsAllVisual,EmptyUnit=@EmptyUnit,IsReachCondition=@IsReachCondition,TaskContent=@TaskContent,TaskRemark=@TaskRemark,ImageType=@ImageType,InitGold=@InitGold,SortNum=@SortNum,IsUseStage=@IsUseStage");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@Name", SqlDbType.VarChar,50),
					new SqlParameter("@StartTime", SqlDbType.DateTime),
					new SqlParameter("@EndTime", SqlDbType.DateTime),
					new SqlParameter("@IsRemind", SqlDbType.Bit,1),
					new SqlParameter("@ID", SqlDbType.Int,4),
					new SqlParameter("@IsAllVisual", SqlDbType.Bit,1),
					new SqlParameter("@EmptyUnit", SqlDbType.Int,4),
					new SqlParameter("@IsReachCondition", SqlDbType.Bit,1),
					new SqlParameter("@TaskContent", SqlDbType.VarChar,100),
					new SqlParameter("@TaskRemark", SqlDbType.VarChar,5000),
					new SqlParameter("@ImageType", SqlDbType.VarChar,50),
					new SqlParameter("@InitGold", SqlDbType.Int),
					new SqlParameter("@SortNum", SqlDbType.Int),
					new SqlParameter("@IsUseStage", SqlDbType.Bit,1)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.StartTime;
            parameters[2].Value = model.EndTime;
            parameters[3].Value = model.IsRemind;
            parameters[4].Value = model.ID;
            parameters[5].Value = model.IsAllVisual;
            parameters[6].Value = model.EmptyUnit;
            parameters[7].Value = model.IsReachCondition;
            parameters[8].Value = model.TaskContent;
            parameters[9].Value = model.TaskRemark;
            parameters[10].Value = model.ImageType;
            parameters[11].Value = model.InitGold;
            parameters[12].Value = model.SortNum;
            parameters[13].Value = model.IsUseStage;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Task ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
};
            parameters[0].Value = ID;

            int rows = DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string IDlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from Task ");
            strSql.Append(" where ID in (" + IDlist + ")  ");
            int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public GtsServer.Model.Task GetModel(int ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 ID,Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage from Task ");
            strSql.Append(" where ID=@ID");
            SqlParameter[] parameters = {
					new SqlParameter("@ID", SqlDbType.Int,4)
};
            parameters[0].Value = ID;

            GtsServer.Model.Task model = new GtsServer.Model.Task();
            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["ID"] != null && ds.Tables[0].Rows[0]["ID"].ToString() != "")
                {
                    model.ID = int.Parse(ds.Tables[0].Rows[0]["ID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["TaskTypeID"] != null && ds.Tables[0].Rows[0]["TaskTypeID"].ToString() != "")
                {
                    model.TaskTypeID = int.Parse(ds.Tables[0].Rows[0]["TaskTypeID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CrateTime"] != null && ds.Tables[0].Rows[0]["CrateTime"].ToString() != "")
                {
                    model.CrateTime = DateTime.Parse(ds.Tables[0].Rows[0]["CrateTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartTime"] != null && ds.Tables[0].Rows[0]["StartTime"].ToString() != "")
                {
                    model.StartTime = DateTime.Parse(ds.Tables[0].Rows[0]["StartTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndTime"] != null && ds.Tables[0].Rows[0]["EndTime"].ToString() != "")
                {
                    model.EndTime = DateTime.Parse(ds.Tables[0].Rows[0]["EndTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsRemind"] != null && ds.Tables[0].Rows[0]["IsRemind"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsRemind"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsRemind"].ToString().ToLower() == "true"))
                    {
                        model.IsRemind = true;
                    }
                    else
                    {
                        model.IsRemind = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["State"] != null && ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    model.State = int.Parse(ds.Tables[0].Rows[0]["State"].ToString());
                }
                if (ds.Tables[0].Rows[0]["IsAllVisual"] != null && ds.Tables[0].Rows[0]["IsAllVisual"].ToString() != "")
                {
                    if ((ds.Tables[0].Rows[0]["IsAllVisual"].ToString() == "1") || (ds.Tables[0].Rows[0]["IsAllVisual"].ToString().ToLower() == "true"))
                    {
                        model.IsAllVisual = true;
                    }
                    else
                    {
                        model.IsAllVisual = false;
                    }
                }
                if (ds.Tables[0].Rows[0]["EmptyUnit"] != null && ds.Tables[0].Rows[0]["EmptyUnit"].ToString() != "")
                {
                    model.EmptyUnit = int.Parse(ds.Tables[0].Rows[0]["EmptyUnit"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        public DataSet GetListByTypeID(int typeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage ");
            strSql.Append(" FROM Task where TaskTypeID=" + typeID + " Order by State,StartTime ");
            return DbHelperSQL.Query(strSql.ToString());
        }


        public DataSet GetListByTypeID(int typeID, int pageIndex, int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select ID,Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage,ROW_NUMBER() OVER(Order by State,StartTime) as RowNumber ");
            strSql.Append(" FROM Task where TaskTypeID=" + typeID + " ) a  where  RowNumber BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize + ";");
            strSql.Append(" select COUNT(1) from Task where TaskTypeID=" + typeID);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage ");
            strSql.Append(" FROM Task ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" Order by State,StartTime ");
            return DbHelperSQL.Query(strSql.ToString());
        }
       
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere, int pageIndex, int pageSize)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select ID,Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage,ROW_NUMBER() OVER(Order by State,StartTime) RowNumber FROM Task ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" ) a where  RowNumber BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize + ";");

            strSql.Append(" select COUNT(1) from Task ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表  找出正在进行中的及比赛结束7天内的比赛
        /// </summary>
        public DataSet GetList(int typeID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select * from Task where TaskTypeID = 2 and (State=1 or (State=2 and datediff(day,EndTime,GETDATE())<=7)) Order by State,EndTime");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得数据列表(比赛公告调用)
        /// </summary>
        public DataSet GetListByTaskTypeID(int pageIndex, int pageSize, string strWhere = "")
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from( select ID,Name,TaskTypeID,IsReachCondition, ROW_NUMBER() OVER(order by id desc) as RowNumber");
            strSql.Append(" FROM Task where state<>2 ");
            if (!string.IsNullOrEmpty(strWhere))
                strSql.Append(strWhere);
            strSql.Append(") Task where  RowNumber BETWEEN " + ((pageIndex - 1) * pageSize + 1) + " and " + pageIndex * pageSize + ";");
            strSql.Append(" select COUNT(1) from Task where state<>2 ");
            if (!string.IsNullOrEmpty(strWhere))
                strSql.Append(strWhere);
            return DbHelperSQL.Query(strSql.ToString());
        }
        public DataSet GetListByPrize()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ID,Name,TaskTypeID");
            strSql.Append(" FROM Task where TaskTypeID='2' and State=1 ");
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" ID,Name,TaskTypeID,CrateTime,StartTime,EndTime,IsRemind,State,IsAllVisual,EmptyUnit,IsReachCondition,TaskContent,TaskRemark,ImageType,InitGold,SortNum,IsUseStage ");
            strSql.Append(" FROM Task ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /*
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetList(int PageSize,int PageIndex,string strWhere)
        {
            SqlParameter[] parameters = {
                    new SqlParameter("@tblName", SqlDbType.VarChar, 255),
                    new SqlParameter("@fldName", SqlDbType.VarChar, 255),
                    new SqlParameter("@PageSize", SqlDbType.Int),
                    new SqlParameter("@PageIndex", SqlDbType.Int),
                    new SqlParameter("@IsReCount", SqlDbType.Bit),
                    new SqlParameter("@OrderType", SqlDbType.Bit),
                    new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
                    };
            parameters[0].Value = "Task";
            parameters[1].Value = "ID";
            parameters[2].Value = PageSize;
            parameters[3].Value = PageIndex;
            parameters[4].Value = 0;
            parameters[5].Value = 0;
            parameters[6].Value = strWhere;	
            return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
        }*/

        #endregion  Method
    }
}

